package com.css.fxfzxczh.modules.fieldWorkManage.InvestigationGroupManage.repository.impl;

import com.css.fxfzxczh.constants.YNEnum;
import com.css.fxfzxczh.modules.eqDisasterLossAssess.houseEconomicLossAssess.entity.HousesDirectEconomicLosses;
import com.css.fxfzxczh.modules.fieldWorkManage.InvestigationGroupManage.entity.InvestigationPersonnel;
import com.css.fxfzxczh.modules.fieldWorkManage.InvestigationGroupManage.entity.InvestigationTeam;
import com.css.fxfzxczh.modules.fieldWorkManage.InvestigationGroupManage.params.PageParam;
import com.css.fxfzxczh.modules.fieldWorkManage.InvestigationGroupManage.repository.InvestigationGroupRepository;
import com.css.fxfzxczh.modules.fieldWorkManage.InvestigationGroupManage.vo.PersonnelVO;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/01/15
 * @Description: 调查组
 */
@Repository
public class InvestigationGroupRepositoryImpl implements InvestigationGroupRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "xczh_investigation_team"; // 表名
    private static final String TABLE_ID = "xit_id"; // 表id字段名

    @Override
    public String getCode(String xsemId) {
        String sql = "select team_code from " + TABLE_NAME + " where  xsem_id = '" + xsemId + "' and del_flag ='0' order by create_time desc limit 1";
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void save(InvestigationTeam param) {
        String sql = "insert into " + TABLE_NAME +
                " (xit_id,xsem_id,team_code,leader," +
                "leader_phone,team_desc,company,team_name," +
                "del_flag,create_user,create_time,leader_id)" +
                " values(:xitId,:xsemId,:teamCode,:leader," +
                ":leaderPhone,:teamDesc,:company,:teamName," +
                ":delFlag,:createUser,:createTime,:leaderId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void update(InvestigationTeam param) {
        String sql = "update " + TABLE_NAME +
                " set leader=:leader,leader_phone=:leaderPhone,team_desc=:teamDesc," +
                "company=:company,team_name=:teamName,update_user=:updateUser,update_time=:updateTime,leader_id=:leaderId" +
                " where del_flag = '0' and "+TABLE_ID+" = '"+param.getXitId()+"'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateXsemId(String xsemId, String convertedXsemId) {
        String sql = "update "+TABLE_NAME+" set xsem_id = '"+xsemId+"'  where xsem_id = '"+convertedXsemId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public String getXsemCode(String xsemId) {
        String sql = "select seismic_code from  xczh_seismic_event_management where  xsem_id = '" + xsemId + "' ";
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<InvestigationTeam> findByLeaderId(String leaderId,String xsemId) {
        String sql = "select a.* from xczh_investigation_team a " +
                "left join xczh_personnel_info_management b on a.leader_id=b.uuid " +
                "where b.uuid='"+leaderId+"'" +
                "and b.xsem_id='"+xsemId+"'" +
                "and a.del_flag='0'" +
                "and b.del_flag='0'";
        List<InvestigationTeam> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvestigationTeam.class));
        return list;
    }

    @Override
    public List<InvestigationPersonnel> findByPersonnelId(String userId,String xsemId) {
        String sql = "select a.* from xczh_investigation_personnel a " +
                "left join xczh_personnel_info_management b on a.personnel_id=b.uuid " +
                "where b.uuid='"+userId+"'" +
                "and b.xsem_id='"+xsemId+"'" +
                "and a.del_flag='0'" +
                "and b.del_flag='0'";
        List<InvestigationPersonnel> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvestigationPersonnel.class));
        return list;
    }

    @Override
    public String getMaxNum(String str) {
        String sql="select max(team_code) from xczh_investigation_team where del_flag = '0' and team_code like '%"+str+"%'";
        String maxNum=jdbcTemplate.queryForObject(sql,String.class);
        return maxNum;
    }

    @Override
    public List<InvestigationTeam> getInvGroupListByXsemId(String xsemId) {
        String sql = "select * from xczh_investigation_team where del_flag = '0' and xsem_id='"+xsemId+"'" ;

        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvestigationTeam.class));
    }

    @Override
    public List<String> getTeamMemberId(String xitId) {
        String sql = "select personnel_id personnelId from xczh_investigation_personnel where del_flag = '0' and investigation_id = '"+xitId+"' and type='组员'" ;
        try {
            return jdbcTemplate.queryForList(sql, String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //根据人员id查询人员所在调查组组
    @Override
    public List<InvestigationTeam> getPersonnelList(String xsemId,String id) {
        String sql = "select b.* from " +
                "(select * from xczh_investigation_personnel where del_flag ='0' and personnel_id = '"+id+"' ) a " +
                " left join " +
                "(select * from xczh_investigation_team where del_flag = '0' ) b " +
                " on a.investigation_id = b.xit_id where  b.xsem_id='"+xsemId+"' " ;
        try {
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(InvestigationTeam.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    @Override
    public InvestigationTeam findById(String param) {
        String sql = "select * from " + TABLE_NAME + " where " + TABLE_ID + " = '" + param + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(InvestigationTeam.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //查寻人员
    @Override
    public List<PersonnelVO> queryPersonnel(String xitId) {
        String sql = "select * from xczh_investigation_personnel where del_flag = '0' and investigation_id = '"+xitId+"' and type='组员'" ;
        try {
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PersonnelVO.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //查询司机
    @Override
    public List<PersonnelVO> querydriver(String xitId) {
        String sql = "select a.*,b.vehicle_number from " +
                "(select * from xczh_investigation_personnel where investigation_id = '"+xitId+"' and " +
                "type='司机' and del_flag = '0') a " +
                "left join " +
                "(select driver_id,vehicle_number from xczh_vehicle_management where del_flag = '0') b " +
                "on a.personnel_id = b.driver_id ";
        try {
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PersonnelVO.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void savePersonnel(InvestigationPersonnel investigationPersonnel) {
        String sql = "INSERT INTO xczh_investigation_personnel (id,investigation_id,personnel_id,person_name,tel,unit,type,gender," +
                "company,del_flag,create_user,create_time)" +
                "values (:id,:investigationId,:personnelId,:personName,:tel,:unit,:type,:gender," +
                ":company,:delFlag,:createUser,:createTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(investigationPersonnel);
        namedParameterJdbcTemplate.update(sql,source);
    }

    @Override
    public void deletePersonnel(String id) {
        String sql = "update xczh_investigation_personnel  set del_flag = ?  where id = ?";
        jdbcTemplate.update(sql, YNEnum.Y.toString(),id);
    }

    //批量新增调查组人员
    @Override
    public void batchSavePersonnel(List<InvestigationPersonnel> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO xczh_investigation_personnel (id,investigation_id,personnel_id,person_name,tel,unit,type,gender," +
                "company,del_flag,create_user,create_time)" +
                "values (:id,:investigationId,:personnelId,:personName,:tel,:unit,:type,:gender," +
                ":company,:delFlag,:createUser,:createTime)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void deleteInvestigationGroup(String id) {
        String sql = "update "+TABLE_NAME+"  set del_flag = ?  where " + TABLE_ID + " = ?";
        jdbcTemplate.update(sql, YNEnum.Y.toString(),id);
    }

    @Override
    public void deleteInvestigationGroupPersonnel(String id) {
        String sql = "update xczh_investigation_personnel  set del_flag = ?  where investigation_id = ?";
        jdbcTemplate.update(sql, YNEnum.Y.toString(),id);
    }

    @Override
    public Map<String, Object> getPage(PageParam queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from "+TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        //组长
        if (!PlatformObjectUtils.isEmpty(queParams.getLeader())) {
            whereSql.append(" and leader like '%" + queParams.getLeader() + "%'");
        }
        //地震事件id
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        //调查组名称
        if (!PlatformObjectUtils.isEmpty(queParams.getTeamName())) {
            whereSql.append(" and team_name like '%" + queParams.getTeamName() + "%'");
        }
        //调查组创建时间
        if (!PlatformObjectUtils.isEmpty(queParams.getStartTime())&!PlatformObjectUtils.isEmpty(queParams.getEndTime())) {
            whereSql.append("and create_time between '"+queParams.getStartTime()+"' and '"+queParams.getEndTime()+"'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(InvestigationTeam.class)).size();
        List<InvestigationTeam> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(InvestigationTeam.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }
}
